** Clean population data, 2000-2015
** JHL 

*************************************
** Set up workspace
*************************************
version 14.0
clear all
set more off

cd "${path_home}"
adopath + ../programs

** log using "${path_log}/c03_county_pop", text replace

*************************************
** Start work here
*************************************
timer on 1 

* Estimates July 1, census April 1 

*************************************
** [1] 0010, July 1 
*************************************

import delimited "${path_big_dta}/us_counties/CO-EST00INT-TOT.csv", clear

gen id=_n
reshape long popestimate@, i(id) j(year) 

save "${path_big_dta}/us_counties/county_pop_0010", replace 

*************************************
** [1] 1014, July 1 
*************************************

* First converted to number from text in excel 

import excel "${path_big_dta}/us_counties/1015_county_pop.xlsx", sheet("Sheet1") firstrow clear
cap drop B C E F
gen id=_n
reshape long y@, i(id) j(year) 

rename y pop

* Some manual corrections to make it match with county later 
replace Geo="Dona Ana County, New Mexico" if Geo=="Doña Ana County, New Mexico"
replace Geo="Dona Ana County, New Mexico" if Geo=="Do?a Ana County, New Mexico"
replace Geo="La Salle Parish, Louisiana" if Geo=="LaSalle Parish, Louisiana"
* Petersburg Borough: 02195 fips, confirmed via documents
replace Geo="Petersburg Census Area, Alaska" if Geo=="Petersburg Borough, Alaska"
* Name change from Shannon County to Oglala Lakota County
replace Geo="Shannon County, South Dakota" if Geo=="Oglala Lakota County, South Dakota" 
* Name change from Wade Hampton Census Area to Kusilvak Census Area
replace Geo="Wade Hampton Census Area, Alaska"  if Geo=="Kusilvak Census Area, Alaska"

split Geo, p(", ") 
rename (Geography1 Geography2) (county state_name) 
merge m:1 state_name using "${path_big_dta}/us_states/state", keep(match) nogen
rename state fips_state
rename stusab state
rename county countyname

* Match county names with fips
merge m:1 state countyname using "${path_big_dta}/us_counties/national_county", keep(match) nogen 

keep year pop statefp countyfp state_name
rename (pop statefp countyfp state_name) (popestimate state county stname) 
save "${path_big_dta}/us_counties/county_pop_1015", replace

*************************************
** [1] Append files 
*************************************

use "${path_big_dta}/us_counties/county_pop_0010", clear
append using "${path_big_dta}/us_counties/county_pop_1015" 

* Note: Difference in 2010 because they are estimates based off of Census that estimates April 1, differences minor, used later (10-14 version) 
drop if year==2010&id!=.
drop if county==0

sort state county year

drop id sumlev region division stname ctyname estima census

rename (state county) (fips_state_code fips_county_code)

save "${path_big_dta}/us_counties/county_pop_0015", replace

*************************************
** Close workspace
*************************************
** log close
timer off 1
timer list 1
